const fs = require('fs')
const path = require('path')
const { Sequelize } = require('sequelize')

async function initDatabase() {
  // 首先连接到MySQL服务器（不指定数据库）
  const config = {
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 3306,
    username: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || 'root',
    dialect: 'mysql',
    dialectOptions: {
      charset: 'utf8mb4'
    },
    logging: false
  }
  
  const sequelizeRoot = new Sequelize('', config.username, config.password, config)
  
  try {
    await sequelizeRoot.authenticate()
    console.log('MySQL服务器连接成功')
    
    // 创建数据库
    await sequelizeRoot.query("CREATE DATABASE IF NOT EXISTS `print_ad_system` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
    console.log('数据库 print_ad_system 创建成功')
    
    await sequelizeRoot.close()
    
    // 现在连接到新创建的数据库
    const sequelize = require('../config/database')
    await sequelize.authenticate()
    console.log('连接到 print_ad_system 数据库成功')
    
    // 读取init.sql文件
    const sqlFile = path.join(__dirname, '../../database/init.sql')
    const sqlContent = fs.readFileSync(sqlFile, 'utf8')
    
    // 移除CREATE DATABASE语句，因为我们已经创建了
    const cleanedSql = sqlContent
      .replace(/CREATE DATABASE.*?;/gi, '')
      .replace(/USE.*?;/gi, '')
    
    // 分割SQL语句
    const statements = cleanedSql
      .split(';')
      .map(stmt => stmt.trim())
      .filter(stmt => stmt.length > 0 && !stmt.startsWith('--'))
    
    console.log(`准备执行 ${statements.length} 条SQL语句...`)
    
    // 执行每条SQL语句
    for (let i = 0; i < statements.length; i++) {
      const statement = statements[i]
      if (statement.trim()) {
        try {
          console.log(`执行第 ${i + 1} 条语句: ${statement.substring(0, 50)}...`)
          await sequelize.query(statement)
        } catch (error) {
          // 忽略一些常见的错误（如表已存在）
          if (error.message.includes('already exists') || 
              error.message.includes('Duplicate entry') ||
              error.message.includes('Table') && error.message.includes('already exists')) {
            console.log(`跳过: ${error.message}`)
          } else {
            console.error(`执行SQL失败: ${statement.substring(0, 100)}...`)
            console.error(error.message)
          }
        }
      }
    }
    
    console.log('数据库初始化完成!')
    
    // 验证表结构
    const [results] = await sequelize.query("DESCRIBE products")
    console.log('\n验证products表结构:')
    results.forEach(column => {
      console.log(`- ${column.Field}: ${column.Type}`)
    })
    
    await sequelize.close()
    
  } catch (error) {
    console.error('数据库初始化失败:', error)
    await sequelizeRoot.close()
  }
}

initDatabase()